/* 
* Copyright (c) 2009, The University of Texas at Austin
* All rights reserved.
*
* Redistribution and use in source and binary forms, with or without modification, 
* are permitted provided that the following conditions are met:
*
* 1. Redistributions of source code must retain the above copyright notice, 
* this list of conditions and the following disclaimer.
*
* 2. Redistributions in binary form must reproduce the above copyright notice, 
* this list of conditions and the following disclaimer in the documentation and/or other materials 
* provided with the distribution.
*
* Neither the name of The University of Texas at Austin nor the names of its contributors may be 
* used to endorse or promote products derived from this software without specific prior written 
* permission.
* 
* THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND ANY EXPRESS OR 
* IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND 
* FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS 
* BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES 
* (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR 
* PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN 
* CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF 
* THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
*/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET ANSI_PADDING ON
GO
CREATE FUNCTION fnSelectAllSequencesForTaxonomyByScientificName(@scientificName varchar(8000))
RETURNS @seqSet TABLE
(
	SeqID int
)
AS
BEGIN
	WITH TaxIDSet (TaxID) AS
	(
		SELECT T1.TaxID FROM Taxonomy AS T1
		WHERE T1.TaxID = (SELECT T.TaxID FROM Taxonomy AS T 
				  INNER JOIN TaxonomyNames AS TN
			          ON T.TaxID = TN.TaxID
			          WHERE TN.ScientificName=@scientificName)
		UNION ALL
		SELECT T2.TaxID FROM Taxonomy AS T2
		INNER JOIN TaxIDSet ON T2.ParentTaxID = TaxIDSet.TaxID
	)
	INSERT INTO @seqSet (SeqID)
	SELECT S.SeqID FROM SequenceMain AS S
	INNER JOIN TaxIDSet AS T
	ON S.TaxID = T.TaxID;
	RETURN
END